package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.*;

public class DbContact implements IFDBContact {
	private Connection con;
		public DbContact()
		{
			con = DbConnection.getInstance().getDBcon();
		}
		
		@Override
		public int getNextContactId() {
			return GetMax.getMaxId("SELECT max(id) from Contact");
		}
		
		@Override
		public ArrayList<Contact> getAllPrivateCustomers() {
			String wClause = "type = 'private' AND status = 'active'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "private");
		}

		@Override
		public Contact getCustomerByCPR(String CPR) {
			String wClause = "cpr = '" + CPR + "' AND status = 'active'";
			System.out.println("Search Customer " + wClause);
			return singleWhere(wClause, "private");
		}

		@Override
		public ArrayList<Contact> getCustomerByName(String name) {
			String wClause = "name LIKE '%" + name + "%' AND status = 'active'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "both");
		}
		
		@Override
		public Contact getCustomerById(int id) {
			String wClause = "c.id = '" + id + "'";
			System.out.println("Search Customer " + wClause);
			return singleWhere(wClause, "both");
		}
		
		@Override
		public ArrayList<Contact> getAllCustomers() {
			String wClause = "status = 'active'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "both");
		}

		@Override
		public ArrayList<Contact> getAllBusinessCustomers() {
			String wClause = "type = 'business' AND status = 'active'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "business");
		}
		
		@Override
		public ArrayList<Contact> getBusinessCustomersByName(String customerName) {
			String wClause = "name LIKE '%" + customerName + "%' AND type = 'business' " +
					"AND status = 'active'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "business");
		}

		@Override
		public ArrayList<Contact> getPrivateCustomersByName(String customerName) {
			String wClause = "name LIKE '%" + customerName + "%' AND type = 'private' " +
					"AND status = 'active'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "private");
		}

		@Override
		public ArrayList<Contact> getCustomerByCVR(String CVR) {
			String wClause = "cvr LIKE '%" + CVR + "%' AND status = 'active'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "business");
		}
		
		@Override
		public ArrayList<Contact> getArchivedCustomers() {
			String wClause = "status = 'archive'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "both");
		}
		
		@Override
		public ArrayList<Contact> getArchivedCustomersByName(String customerName) {
			String wClause = "name LIKE '%" + customerName + "%' AND status = 'archive'";
			System.out.println("Search Customer " + wClause);
			return miscWhere(wClause, "both");
		}

		@Override
		public int insertCustomer(Contact customer) throws Exception  {
			int nextID = GetMax.getMaxId("SELECT max(id) from Contact");
			nextID++;
			int rc = -1;
			String insertQuery="INSERT INTO Contact(id, address, city, zipCode, " +
								"phoneNo, eMail, status, type";
			String valueQuery = "VALUES('"+
					nextID  + "','"  +
					customer.getAddress() + "','"  +
					customer.getCity() + "','"  +
					customer.getZipCode() + "','"  +
					customer.getPhoneNO() + "','"  +
					customer.getEMail() + "','"  +
					customer.getStatus().toLowerCase() + "','";

			String queryContact = "";
			
			String queryCustomer = "";
			
			if(customer instanceof Private)
			{
				queryContact = insertQuery + ") " + valueQuery + "private')";
				
				Private privateCustomer = (Private) customer;
				
				insertQuery="INSERT INTO Private(id, cpr, name, surname";
				valueQuery="VALUES('"+nextID + "','"  +
						privateCustomer.getCPR() + "','"  +
						privateCustomer.getName() + "','" +
						privateCustomer.getSurname()  + "'";
				
				queryCustomer = insertQuery + ") " + valueQuery + ")";
			}
			else
			{
				queryContact = insertQuery + ") " + valueQuery + "business')";
				
				Business businessCutomer = (Business) customer;
				
				insertQuery="INSERT INTO Business(id, cvr, name";
				valueQuery="VALUES('"+nextID + "','"  +
						businessCutomer.getCVR() + "','"  +
						businessCutomer.getName() + "'";
				
				queryCustomer = insertQuery + ") " + valueQuery + ")";
			}
			
			System.out.println("insert Contact: " + queryContact);
			System.out.println("insert Customer: " + queryCustomer);
			try{ // insert new customer
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(queryContact);
				rc = stmt.executeUpdate(queryCustomer);
				stmt.close();
			}//end try
			catch(SQLException ex){
				System.out.println("Customer not created");
				throw new Exception("Customer is not inserted correctly");
			}
			return(rc);
		}

		@Override
		public int updateCustomer(Contact customer) {
			int rc=-1;

			String queryContact="UPDATE Contact SET "+
					"address = '" + customer.getAddress() + "', "+
					"city = '" + customer.getCity() + "', " +
					"zipCode = '" + customer.getZipCode() + "', " +
					"phoneNo = '" + customer.getPhoneNO() + "', " +
					"eMail = '" + customer.getEMail() + "', " +
					"status = '" + customer.getStatus().toLowerCase() + "'" +
					" WHERE id = '"+ customer.getId() + "'";
			
			String queryCustomer="";
			
			if(customer instanceof Private)
			{
				Private privateCustomer = (Private) customer;
				queryCustomer="UPDATE Private SET "+
						"cpr = '"+ privateCustomer.getCPR()+ "', " +
						"name = '"+ privateCustomer.getName() + "', " +
						"surname = '"+ privateCustomer.getSurname() + "'" +
						" WHERE id = '"+ privateCustomer.getId() + "'";
			}
			else
			{
				Business businessCustomer = (Business) customer;
				queryCustomer="UPDATE Business SET "+
						"cvr = '"+ businessCustomer.getCVR()+ "', " +
						"name = '"+ businessCustomer.getName() + "'" +
						" WHERE id = '"+ businessCustomer.getId() + "'";
			}
			
			System.out.println("Update Contact: " + queryContact);
			System.out.println("Update Customer: " + queryCustomer);
			try{ // update Contact + Private
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(queryContact);
				rc = stmt.executeUpdate(queryCustomer);

				stmt.close();
			}//end try
			catch(Exception ex){
				System.out.println("Update exception in Customer db: "+ex);
			}
			return(rc);
		}

		@Override
		public int deleteCustomer(Contact customer) {
			int rc=-1;

			String query="DELETE FROM Private WHERE id = '" +
					customer.getId() + "'";
			System.out.println(query);
			try{ // delete from private
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				stmt.close();  
			}//end try	
			catch(Exception ex){
				System.out.println("Delete exception in Private db: "+ex);
			}
			return(rc);
		}

		//private methods

		//michWere is used whenever we want to select more than one private customer
		private ArrayList<Contact> miscWhere(String wClause, String type)
		{
			ResultSet results;
			ArrayList<Contact> list = new ArrayList<Contact>();	
			String queryPrivate =  "";
			String queryBusiness = "";

			if(type=="private")
			{
				queryPrivate =  buildPrivateQuery(wClause);
				try{ // read the Private Customer from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(queryPrivate);

					while( results.next() )
					{
						Contact custObj = new Contact();
						custObj = buildCustomer(results);				
						list.add(custObj);		
					}//end while
					stmt.close();     

				}//end try	
				catch(Exception e){
					System.out.println("Query exception - select private: "+e);
					e.printStackTrace();
				}
			}
			else
			{
				if(type=="business")
				{
					queryBusiness = buildBusinessQuery(wClause);
					try{ // read the Business Customer from the database
						Statement stmt = con.createStatement();
						stmt.setQueryTimeout(5);
						results = stmt.executeQuery(queryBusiness);

						while( results.next() )
						{
							Contact custObj = new Contact();
							custObj = buildCustomer(results);				
							list.add(custObj);		
						}//end while
						stmt.close();     

					}//end try	
					catch(Exception e){
						System.out.println("Query exception - select business: "+e);
						e.printStackTrace();
					}
				}
				else
				{
					queryPrivate =  buildPrivateQuery(wClause);
					queryBusiness = buildBusinessQuery(wClause);
					try{ // read the Private and Business Customer from the database
						Statement stmt = con.createStatement();
						stmt.setQueryTimeout(5);
						results = stmt.executeQuery(queryPrivate);

						while( results.next() )
						{
							Contact custObj = new Contact();
							custObj = buildCustomer(results);				
							list.add(custObj);		
						}//end while
						stmt.close();     

					}//end try	
					catch(Exception e){
						System.out.println("Query exception - select both private: "+e);
						e.printStackTrace();
					}

					try{ // read the BusinessCustomer from the database
						Statement stmt = con.createStatement();
						stmt.setQueryTimeout(5);
						results = stmt.executeQuery(queryBusiness);

						while( results.next() )
						{
							Contact custObj = new Contact();
							custObj = buildCustomer(results);				
							list.add(custObj);		
						}//end while
						stmt.close();     

					}//end try	
					catch(Exception e){
						System.out.println("Query exception - select both business: "+e);
						e.printStackTrace();
					}
				}
			}			
			return list;
		}

		//Singlewhere to select only one Private Customer 	
		private Contact singleWhere(String wClause, String type)
		{
			ResultSet results;
			Contact custObj = new Contact();
			String query = "";
			
			if(type.equals("private"))
			{
				query =  buildPrivateQuery(wClause);
				System.out.println(query);
				try{ // read the Private Customer from the database
					Statement stmt = con.createStatement();
					stmt.setQueryTimeout(5);
					results = stmt.executeQuery(query);

					if( results.next() ){
						custObj = buildCustomer(results);
						stmt.close();
					}		
					else{ //no customer was found
						custObj = null;
					}
				}
				catch(Exception e){
					System.out.println("Query exception - select private: "+e);
				}
			}
			else
			{
				if(type=="business")
				{
					query =  buildBusinessQuery(wClause);
					System.out.println(query);
					try{ // read the Business Customer from the database
						Statement stmt = con.createStatement();
						stmt.setQueryTimeout(5);
						results = stmt.executeQuery(query);
	
						if( results.next() ){
							custObj = buildCustomer(results);
							stmt.close();
						}		
						else{ //no customer was found
							custObj = null;
						}
					}
					catch(Exception e){
						System.out.println("Query exception - select business: "+e);
					}
				}
				else
				{
					query =  buildPrivateQuery(wClause);
					System.out.println(query);
					try{ // read the Private Customer from the database
						Statement stmt = con.createStatement();
						stmt.setQueryTimeout(5);
						results = stmt.executeQuery(query);

						if( results.next() ){
							custObj = buildCustomer(results);
							stmt.close();
						}		
						else{ //no customer was found
							custObj = null;
						}
					}
					catch(Exception e){
						System.out.println("Query exception - select private: "+e);
					}
					if(custObj==null)
					{
						query =  buildBusinessQuery(wClause);
						System.out.println(query);
						try{ // read the Business Customer from the database
							Statement stmt = con.createStatement();
							stmt.setQueryTimeout(5);
							results = stmt.executeQuery(query);
		
							if( results.next() ){
								custObj = buildCustomer(results);
								stmt.close();
							}		
							else{ //no customer was found
								custObj = null;
							}
						}
						catch(Exception e){
							System.out.println("Query exception - select business: "+e);
						}
					}
				}
			}
			return custObj;
		}
		
		//Build query for selecting from Private Customers
		private String buildPrivateQuery(String wClause)
		{
			String query="SELECT c.id, address, city, zipCode, phoneNo, eMail, status, type, "
					+ "cpr, name, surname FROM Contact c, Private p "
					+ "WHERE p.id = c.id";
			
			if (wClause.length()>0)
				query=query+" AND " + wClause;

			return query;
		}
		
		//Build query for selecting from Business Customers
		private String buildBusinessQuery(String wClause)
		{
			String query="SELECT c.id, address, city, zipCode, phoneNo, eMail, status, type, "
					+ "cvr, name FROM Contact c, Business b "
					+ "WHERE b.id = c.id";
			
			if (wClause.length()>0)
				query=query+" AND " + wClause;

			return query;
		}

		//method to build a Customer object
		private Contact buildCustomer(ResultSet results) throws SQLException
		{
			Contact customerObj = new Contact();
			if(results.getString("type").equals("private"))
			{
				customerObj = buildPrivateCustomer(results);
			}
			else
			{
				customerObj = buildBusinessCustomer(results);
			}
			return customerObj;
		}
		
		//build a Private type of customer
		private Private buildPrivateCustomer(ResultSet results)
		{   
			Private customerObj = new Private();
			try{ // the columns from the table customer  are used
				customerObj.setId(results.getInt("id"));
				customerObj.setAddress(results.getString("address"));
				customerObj.setCity(results.getString("city"));
				customerObj.setZipCode(results.getString("zipCode"));
				customerObj.setPhoneNO(results.getString("phoneNo"));
				customerObj.setEMail(results.getString("eMail"));
				customerObj.setStatus(results.getString("status"));
				customerObj.setCPR(results.getString("cpr"));
				customerObj.setName(results.getString("name"));
				customerObj.setSurname(results.getString("surname"));
			}
			catch(Exception e)
			{
				System.out.println("error in building the private customer object");
			}
			return customerObj;
		}
		
		//build Business type of customer
		private Business buildBusinessCustomer(ResultSet results)
		{   
			Business customerObj = new Business();
			try{ // the columns from the table customer  are used
				customerObj.setId(results.getInt("id"));
				customerObj.setAddress(results.getString("address"));
				customerObj.setCity(results.getString("city"));
				customerObj.setZipCode(results.getString("zipCode"));
				customerObj.setPhoneNO(results.getString("phoneNo"));
				customerObj.setEMail(results.getString("eMail"));
				customerObj.setStatus(results.getString("status"));
				customerObj.setCVR(results.getString("cvr"));
				customerObj.setName(results.getString("name"));
			}
			catch(Exception e)
			{
				System.out.println("error in building the business customer object");
			}
			return customerObj;
		}
}
